﻿using FileImportSystem2.Db.Entities;
using Microsoft.VisualBasic;
using MiniExcelLibs;
using SQLite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;

namespace FileImportSystem2.Services
{
    internal class FileImporterService
    {
        public readonly string _dbPath;
        private readonly string _dbType;
        private readonly string _dbFd;
        private readonly string _filePath;
        private readonly SQLiteConnection _db;
        private readonly string _dateFull = "0000-00-00";
        private readonly string _dateMonth = "0000-00";
        private readonly int _batchSize = 10000;
        static readonly object _lockObj = new object();

        #region 数据库初始化
        /// <summary>
        /// 根据文件路径，初始化导入服务
        /// </summary>
        /// <param name="filePath">导入文件路径</param>
        public FileImporterService(string filePath)
        {
            // 需要导入的文件路径
            _filePath = filePath;
            // 数据库路径
            string fName = Path.GetFileNameWithoutExtension(filePath);
            if (fName.IndexOf("匹配") >= 0)
            {
                _dbType = "匹配";
                // 匹配文件，导入主数据库
                _dbPath = AppConfig.dbMainPath;
                _dbFd = Path.GetDirectoryName(_dbPath) ?? string.Empty;

            }
            else
            {
                _dbType = "数据";
                _dbFd = AppConfig.dbMonFd;
                (_dateFull, _dateMonth) = FileNameServer.getDateFromFilePath(filePath);
                _dbPath = Path.Combine(_dbFd, _dateMonth + ".db");

            }
            // 初始化数据库连接
            if (!Directory.Exists(_dbFd))
            {
                Directory.CreateDirectory(_dbFd);
            }
            _db = new SQLiteConnection(_dbPath);

            switch (_dbType)
            {
                case "匹配":
                    // 创建匹配表
                    _db.CreateTable<SenderMatch>();
                    _db.CreateTable<ShenzhouMatch>();
                    break;
                case "数据":
                    // 启用WAL
                    //_db.ExecuteScalar<string>("PRAGMA journal_mode=WAL;");
                    //_db.Execute("PRAGMA journal_mode=WAL;");
                    // 创建数据表
                    _db.CreateTable<SenderData>();
                    _db.CreateTable<ShenzhouData>();
                    _db.CreateTable<OutboundInvoiceData>();
                    _db.CreateTable<CenterTransferFeeData>();
                    _db.CreateTable<UnMatchedData>();
                    break;
                default:
                    throw new Exception("未知的数据库类型");
            }
        }



        #endregion

        #region 文件类型映射以及文件导入


        public Task<(bool res, string mark, int ImpNum)> RunTask(string filePath)
        {
            return Task.Run(() => Import(filePath));
        }

        /// <summary>
        /// 导入文件: 默认初始化文件路径
        /// </summary>
        /// <returns></returns>
        public (bool res, string mark, int ImpNum) Import()
        {
            string filePath = _filePath;
            return Import(filePath);
        }

        /// <summary>
        /// 导入文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns>是否导入成功</returns>
        public (bool res, string mark, int ImpNum) Import(string filePath)
        {
            try
            {
                var fileName = Path.GetFileName(filePath);
                var fileType = getTypeFromFilePath(fileName);
                // 提取文件中的日期信息
                (string dateFull, string dateMonth) = FileNameServer.getDateFromFilePath(filePath);
                if (dateMonth != _dateMonth)
                {
                    return (false, $"文件名中日期信息与数据库初始化日期不符: {fileName}：{dateMonth}/{_dateMonth}", 0);
                }
                // 根据文件类型调用不同的导入方法
                switch (fileType)
                {
                    case "匹配":
                        return ImportMatchData(filePath);
                    case "发件":
                        return ImportSenderData(filePath, dateFull);
                    case "神州":
                        return ImportShenzhouData(filePath, dateFull);
                    case "出港账单":
                        return ImportOutboundData(filePath, dateFull);
                    case "中转费":
                        return ImportCenterTransferData(filePath, dateFull);
                    case "UnMatched":
                        return ImportUnMatchData(filePath);
                    default:
                        return (false, $"未知的文件类型: {fileName}", 0);
                }
            }
            catch (Exception ex)
            {
                return (false, ex.Message, 0);
            }
        }

        /// <summary>
        /// 文件名与表名映射规则
        /// </summary>
        private static readonly Dictionary<string, string>
            _tableMapping = new(StringComparer.OrdinalIgnoreCase)
        {
            // 示例映射规则
            {"UnMatched","UnMatched"}
            ,{ "匹配","匹配"}
            ,{"发件","发件" }
            ,{"神州","神州" }
            ,{"出港账单","出港账单"}
            ,{"中转费","中转费"}
        };

        /// <summary>
        /// 根据文件路径，确定文件类型
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static string getTypeFromFilePath(string filePath)
        {
            // 优先级1：文件名（不含扩展名）
            string fileName = Path.GetFileNameWithoutExtension(filePath);

            // 检查是否直接匹配
            if (_tableMapping.TryGetValue(fileName, out var mapping))
                return mapping;

            // 遍历所有映射规则，检查文件名或者目录前缀匹配
            foreach (var key in _tableMapping.Keys)
            {
                if (fileName.Contains(key, StringComparison.CurrentCulture))
                    return _tableMapping[key];
            }

            return "NA";  // 未匹配到规则
        }
        #endregion

        /// <summary>
        /// 导入匹配数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportMatchData(string filePath)
        {
            // 创建数据表
            _db.CreateTable<SenderMatch>();
            _db.CreateTable<ShenzhouMatch>();
            // 读取文件内容
            var rowsSender = MiniExcel.Query(filePath, useHeaderRow: true, sheetName: "发件");
            var rowsShenzhou = MiniExcel.Query(filePath, useHeaderRow: true, sheetName: "神州");
            int numDb = 0;
            // 插入数据
            var listSender = new List<SenderMatch>();
            foreach (var row in rowsSender)
            {
                var item = new SenderMatch
                {
                    扫描人 = row.扫描人,
                    分部 = row.分部
                };
                if (item.扫描人 != null && item.分部.Length > 0) { listSender.Add(item); numDb++; }
            }
            lock (_lockObj) { _db.InsertAll(listSender, "OR Replace", true); }


            var listShenzhou = new List<ShenzhouMatch>();
            foreach (var row in rowsShenzhou)
            {
                var itemShenzhou = new ShenzhouMatch
                {
                    结算对象 = row.结算对象,
                    分部 = row.分部
                };
                if (itemShenzhou.结算对象 != null && itemShenzhou.分部.Length > 0) { listShenzhou.Add(itemShenzhou); numDb++; }
            }
            lock (_lockObj) { _db.InsertAll(listShenzhou, "OR Replace", true); }


            return (true, string.Empty, numDb);
        }


        /// <summary>
        /// 导入未匹配数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportUnMatchData(string filePath)
        {
            // 创建数据表
            _db.CreateTable<UnMatchedData>();
            // 读取文件内容
            var rows = MiniExcel.Query(filePath, useHeaderRow: true);




            // 插入数据
            var ListDb = new List<UnMatchedData>();
            int numDb = 0;
            foreach (IDictionary<string, object> row in rows.Select(v => (IDictionary<string, object>)v))
            {
                string 单号;
                string 结算对象;
                string 分部;
                string 发件仓;
                //
                if (!row.TryGetValue("单号", out var obj单号)) { continue; } else { 单号 = (string)obj单号; }
                if (!row.TryGetValue("结算对象", out var obj结算对象)) { 结算对象 = string.Empty; } else { 结算对象 = (string)obj结算对象; }
                if (!row.TryGetValue("分部", out var obj分部)) { 分部 = string.Empty; } else { 分部 = (string)obj分部; }
                if (!row.TryGetValue("发件仓", out var obj发件仓)) { 发件仓 = string.Empty; } else { 发件仓 = (string)obj发件仓; }

                // 检查数据库中是否已存在该单号
                var existingItem = _db.Table<UnMatchedData>()
                                         .FirstOrDefault(x => x.单号 == (string)单号);

                // 替换空值为数据库中的值
                if (existingItem != null)
                {
                    if (string.IsNullOrEmpty(结算对象)) { 结算对象 = existingItem.结算对象; }
                    if (string.IsNullOrEmpty(分部)) { 分部 = existingItem.分部; }
                    if (string.IsNullOrEmpty(发件仓)) { 发件仓 = existingItem.发件仓; }

                }

                // 如果值均为空，则跳过该条记录
                if (string.IsNullOrEmpty(结算对象) && string.IsNullOrEmpty(分部) && string.IsNullOrEmpty(发件仓))
                {
                    continue;
                }

                var item = new UnMatchedData
                {
                    单号 = 单号,
                    结算对象 = 结算对象,
                    分部 = 分部,
                    发件仓 = 发件仓,
                };
                //
                if (item.单号 != null) { ListDb.Add(item); numDb++; }
                //
                if (ListDb.Count >= _batchSize)
                {
                    lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); }
                    ListDb.Clear();
                }
            }

            if (ListDb.Count > 0) { lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); } }

            return (true, string.Empty, numDb);
        }


        /// <summary>
        /// 导入发件数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportSenderData(string filePath, string dateFull)
        {
            // 创建数据表
            _db.CreateTable<SenderData>();
            // 读取文件内容
            var rows = MiniExcel.Query(filePath, useHeaderRow: true);
            // 插入数据
            var ListDb = new List<SenderData>();
            int numDb = 0;

            foreach (IDictionary<string, object> row in rows.Select(v => (IDictionary<string, object>)v))
            {
                // 补充字段
                string[] keys = { "时间", "运单号", "扫描人", "发件仓" };
                checkRowBt(row, keys);
                // 转换数据
                var item = new SenderData
                {
                    日期 = dateFull,

                    时间 = ((DateTime)row["时间"]).ToString("yyyy-MM-dd hh:mm:ss"),
                    单号 = (string)row["单号"],
                    扫描人 = (string)row["扫描人"],
                    发件仓 = (string)row["发件仓"],

                    ImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"),
                    IsMatched = 0
                };
                if (item.单号 != null) { ListDb.Add(item); numDb++; }
                if (ListDb.Count >= _batchSize)
                {
                    lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); }
                    ListDb.Clear();
                }
            }

            if (ListDb.Count > 0) { lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); } }

            return (true, string.Empty, numDb);

        }


        /// <summary>
        /// 导入神州数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportShenzhouData(string filePath, string dateFull)
        {
            // 创建数据表
            _db.CreateTable<ShenzhouData>();
            // 读取文件内容
            var rows = MiniExcel.Query(filePath, useHeaderRow: true, startCell: "A2");
            // 插入数据
            var ListDb = new List<ShenzhouData>();
            int numDb = 0;

            foreach (IDictionary<string, object> row in rows.Select(v => (IDictionary<string, object>)v))
            {
                // 补充字段
                string[] keys = { "运单号", "扫描时间", "结算对象", "分部", "运单发放", "揽收业务员", "结算重量", "目的省", "目的市", "运单使用网点", "备注", "发件仓" };
                checkRowBt(row, keys);
                // 转换数据
                var item = new ShenzhouData
                {
                    日期 = dateFull,

                    单号 = (string)row["运单号"],
                    扫描时间 = ((DateTime)row["扫描时间"]).ToString("yyyy-MM-dd hh:mm:ss"),
                    结算对象 = (string)row["结算对象"],
                    分部 = (string)row["分部"],
                    运单发放 = (string)row["运单发放"],
                    揽收业务员 = (string)row["揽收业务员"],
                    结算重量 = (double)row["结算重量"],
                    目的省 = (string)row["目的省"],
                    目的市 = (string)row["目的市"],
                    运单使用网点 = (string)row["运单使用网点"],
                    备注 = (string)row["备注"],
                    发件仓 = (string)row["发件仓"],


                    ImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"),
                    Is分部Matched = 0,
                    Is发件仓Matched = 0
                };
                if (item.单号 != null) { ListDb.Add(item); numDb++; }
                if (ListDb.Count >= _batchSize)
                {
                    lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); }
                    ListDb.Clear();
                }
            }

            if (ListDb.Count > 0) { lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); } }

            return (true, string.Empty, numDb);
        }


        /// <summary>
        /// 导入出港账单
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportOutboundData(string filePath, string dateFull)
        {
            // 创建数据表
            _db.CreateTable<OutboundInvoiceData>();
            // 读取文件内容
            var rows = MiniExcel.Query(filePath, useHeaderRow: true);
            // 插入数据
            var ListDb = new List<OutboundInvoiceData>();
            int numDb = 0;
            foreach (IDictionary<string, object> row in rows.Select(v => (IDictionary<string, object>)v))
            {
                // 补充字段
                string[] keys = { "运单号", "交件时间", "结算对象", "分部", "电子面单账号名称", "总费用_元", "派费合计", "中转费合计", "退件中转费", "退件操作费", "退件费", "结算重量_kg", "目的地省", "目的地市", "面单使用网点" };
                checkRowBt(row, keys);

                double 中转费合计 = (double)row["中转费合计"];
                double 退件中转费 = (Double)row["退件中转费"];
                double 退件操作费 = (Double)row["退件操作费"];
                double 退件费 = (Double)row["退件费"];
                double 中心中转费 = 中转费合计 - 退件中转费 - 退件操作费 - 退件费;
                // 转换数据
                var item = new OutboundInvoiceData
                {
                    日期 = dateFull,

                    单号 = (string)row["运单号"],
                    交件时间 = (string)row["交件时间"],
                    结算对象 = (string)row["结算对象"],
                    分部 = (string)row["分部"],
                    电子面单账号名称 = (string)row["电子面单账号名称"],
                    总费用_元 = (Double)row["总费用(元)"],
                    派费合计 = (Double)row["派费合计"],
                    中转费合计 = 中转费合计,
                    退件中转费 = 退件中转费,
                    退件操作费 = 退件操作费,
                    退件费 = 退件费,
                    结算重量_kg = (Double)row["结算重量(kg)"],
                    目的地省 = (string)row["目的地省"],
                    目的地市 = (string)row["目的地市"],
                    面单使用网点 = (string)row["面单使用网点"],

                    中心中转费 = 中心中转费,
                    ImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"),
                    IsMatched = 0
                };
                if (item.单号 != null) { ListDb.Add(item); numDb++; }
                if (ListDb.Count >= _batchSize)
                {
                    lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); }
                    ListDb.Clear();
                }
            }

            if (ListDb.Count > 0) { lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); } }

            return (true, string.Empty, numDb);
        }


        /// <summary>
        /// 中心中转费
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private (bool res, string mark, int ImpNum) ImportCenterTransferData(string filePath, string dateFull)
        {
            // 创建数据表
            _db.CreateTable<CenterTransferFeeData>();
            // 读取文件内容
            var rows = MiniExcel.Query(filePath, useHeaderRow: true);
            // 插入数据
            var ListDb = new List<CenterTransferFeeData>();
            int numDb = 0;
            foreach (IDictionary<string, object> row in rows.Select(v => (IDictionary<string, object>)v))
            {
                // 补充字段
                string[] keys = { "运单编号", "中心到件时间", "结算对象", "分部", "客户名称", "中转费合计", "结算重量", "目的省份", "目的网点", "运单使用网点", "班次类型", "直链类型" };
                checkRowBt(row, keys);
                // 转换数据
                var item = new CenterTransferFeeData
                {
                    日期 = dateFull,

                    单号 = (string)row["运单编号"],
                    中心到件时间 = (string)row["中心到件时间"],
                    结算对象 = (string)row["结算对象"],
                    分部 = (string)row["分部"],
                    客户名称 = (string)row["客户名称"],
                    中转费合计 = (double)row["中转费合计"],
                    结算重量 = (double)row["结算重量"],
                    目的省份 = (string)row["目的省份"],
                    目的网点 = (string)row["目的网点"],
                    运单使用网点 = (string)row["运单使用网点"],
                    班次类型 = (string)row["班次类型"],
                    直链类型 = (string)row["直链类型"],



                    ImportTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"),
                    IsMatched = 0
                };
                if (item.单号 != null) { ListDb.Add(item); numDb++; }
                if (ListDb.Count >= _batchSize)
                {
                    lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); }
                    ListDb.Clear();
                }
            }

            if (ListDb.Count > 0) { lock (_lockObj) { _db.InsertAll(ListDb, "OR Replace", true); } }

            return (true, string.Empty, numDb);
        }


        private void checkRowBt(IDictionary<string, object> row, string[] keys, int beizhuNum = 3)
        {
            // 核实标题
            foreach (string key in keys)
            {
                if (!row.TryGetValue(key, out _)) { row.Add(key, ""); }
            }

            // 增补备注
            for (int i = 0; i < beizhuNum; i++)
            {
                if (!row.TryGetValue($"备注{i + 1}", out _)) { row.Add($"备注{i + 1}", ""); }
            }

        }
    }
}
